# -*- coding:utf-8 -*-
import xlrd

# 运行前请删除之前生成的文件，或根据需要增加判断代码，每次自动删除
if __name__ == '__main__':
    # 文件绝对路径（表头：表名，表注释，列名，列类型（一般为STRING），列注释）
    excel_path = 'C:\\Users\\shumei\\Desktop\\dwd_schema.xlsx'
    data = xlrd.open_workbook(excel_path)
    dwd_schema_sheet = data.sheet_by_name("Sheet1")
    sql_ddl = ""
    # 起始行数2
    start_idx = 1
    # i从0开始(rows -1 忽略最后一行单独为一张表的情况)
    file_path = "D:\\sql_ddl_data.txt"
    for i in range(1, dwd_schema_sheet.nrows - 1):
        # 表名与下一行表名
        v_tb_name = dwd_schema_sheet.cell(i, 0).value
        v_tb_name_plus_1 = dwd_schema_sheet.cell(i + 1, 0).value
        if v_tb_name != v_tb_name_plus_1:
            # 当前表名与下一行表名不同，则此处分割
            end_idx = i
            # 当前表名
            tb_name = v_tb_name
            # 当前表注释
            tb_comment = dwd_schema_sheet.cell(end_idx, 1).value
            # 当前表字段
            tb_col = dwd_schema_sheet.col_values(2, start_idx, end_idx + 1)
            # 当前表字段类型
            tb_col_type = dwd_schema_sheet.col_values(3, start_idx, end_idx + 1)
            # 当前表字段注释
            tb_col_comment = dwd_schema_sheet.col_values(4, start_idx, end_idx + 1)
            col_ddl = [i + ' ' + j + ' COMMENT \'' + k + '\'' for i, j, k in zip(tb_col, tb_col_type, tb_col_comment)]
            title = "\n--#########表名：{}，表注释：{}#########\n".format(tb_name, tb_comment)
            # ddl语句
            sql_ddl = "CREATE TABLE " + tb_name + "( \n" + "\n,".join(
                col_ddl) + "\n)\n" + "COMMENT \'" + tb_comment + "\';"
            # print(sql_ddl)
            # 追加写入txt文件
            with open(file_path, "a+") as f:
                f.write(title)
                f.write(sql_ddl)
            start_idx = end_idx + 1
    # 最后一张表单独处理
    tb_name_end = dwd_schema_sheet.cell(dwd_schema_sheet.nrows - 1, 0).value
    tb_comment_end = dwd_schema_sheet.cell(dwd_schema_sheet.nrows - 1, 1).value
    # 当前表字段注释
    tb_col_comment_end = dwd_schema_sheet.col_values(4, start_idx, dwd_schema_sheet.nrows)
    tb_col_type_end = dwd_schema_sheet.col_values(3, start_idx, dwd_schema_sheet.nrows)
    tb_col_end = dwd_schema_sheet.col_values(2, start_idx, dwd_schema_sheet.nrows)
    col_ddl_end = [m + ' ' + n + ' COMMENT \'' + o + '\'' for m, n, o in
                   zip(tb_col_end, tb_col_type_end, tb_col_comment_end)]
    title_end = "\n--#########表名：{}，表注释：{}#########\n".format(tb_name_end, tb_comment_end)
    sql_ddl_end = "CREATE TABLE " + tb_name_end + "( \n" + "\n,".join(
        col_ddl_end) + "\n)\n" + "COMMENT \'" + tb_comment_end + "\';"
    # print(sql_ddl_end)
    with open(file_path, "a+") as f:
        f.write(title_end)
        f.write(sql_ddl_end)
    print("SUCCESS!文件路径为：{}".format(file_path))
